import { SelectQueryBuilder } from 'typeorm';
import { SqlQueryDto, SqlWhereConditionEnum } from '../type/app.dto';
import { forOwn, isNil } from 'lodash';
import { str2array } from './trans';

type SqlQueryBuilderOption = {
    queryBuilder: SelectQueryBuilder<any>,
    query: SqlQueryDto,
    table_name?: string,
}
/**
 * 处理 where order 
 * @param option 
 */
export function whereAndOrderQueryBuilder(option: SqlQueryBuilderOption
): void {
    const delimiters = [',', ';', ' ']; // 拆分字符串 星号、问号、竖线和点号
    if (option.query?.order) {
        forOwn(option.query.order, (v: any, k: string) => {
            // option.queryBuilder.addOrderBy(`convert(${k} using GBK)`, v);    
            option.queryBuilder.addOrderBy(k, v);    
            /* if (k.endsWith('_c') || k === 'remark' ) {
                option.queryBuilder.addOrderBy(`convert(${k} using GBK)`, v);    
            } else {
                option.queryBuilder.addOrderBy(k, v);    
            } */
        });
    }
    if (option.query.where) {
        let num = 0; // typeorm 动态传参，参数名不能相同
        forOwn(option.query.where, (v: any, field: string) => {
            num++;
            // console.log('v: ', v);
            // console.log('field : ', field);
            forOwn(v, (value: any, condition: string) => {
                // console.log('value: ', value);
                // console.log('condition : ', condition);

                if (condition === SqlWhereConditionEnum.between) {
                    option.queryBuilder.andWhere(isNil(option.table_name) ? `${field} between :val${num} and :val${num+1}`
                    : `${option.table_name}.${field} between :val${num} and :val${num+1}`, {
                        [`val${num}`]: value[0],
                        [`val${num+1}`]: value[1],
                    });
                    num++;
                }
                if (condition === SqlWhereConditionEnum.contains) {
                    option.queryBuilder.andWhere(isNil(option.table_name) ? `${field} like :val${num}` 
                    : `${option.table_name}.${field} like :val${num}` , {
                        [`val${num}`]: `%${value}%`,
                    });
                }
                if (condition === SqlWhereConditionEnum.equals) {
                    if (isNil(value) || value === 'null') {
                        option.queryBuilder.andWhere(isNil(option.table_name) ? `${field} is null`
                    : `${option.table_name}.${field} is null`);
                    } else {
                        option.queryBuilder.andWhere(isNil(option.table_name) ? `${field} = :val${num}` 
                            : `${option.table_name}.${field} = :val${num}`, {
                            [`val${num}`]: value,
                        });
                    }
                }
                if (condition === SqlWhereConditionEnum.gt) {
                    option.queryBuilder.andWhere(isNil(option.table_name) ? `${field} > :val${num}` 
                    : `${option.table_name}.${field} > :val${num}`, {
                        [`val${num}`]: value,
                    });
                }
                if (condition === SqlWhereConditionEnum.gte) {
                    option.queryBuilder.andWhere(isNil(option.table_name) ? `${field} >= :val${num}` 
                    : `${option.table_name}.${field} >= :val${num}`, {
                        [`val${num}`]: value,
                    });
                }
                if (condition === SqlWhereConditionEnum.in) {
                    const valueArray = typeof(value) === 'string' ? str2array(value, delimiters) : value;
                    option.queryBuilder.andWhere(isNil(option.table_name) ? `${field} in (:...val${num})`
                    : `${option.table_name}.${field} in (:...val${num})`, {
                        [`val${num}`]: valueArray,
                    });
                }
                if (condition === SqlWhereConditionEnum.lt) {
                    option.queryBuilder.andWhere(isNil(option.table_name) ? `${field} < :val${num}` 
                    : `${option.table_name}.${field} < :val${num}`, {
                        [`val${num}`]: value,
                    });
                }
                if (condition === SqlWhereConditionEnum.lte) {
                    option.queryBuilder.andWhere(isNil(option.table_name) ? `${field} <= :val${num}`
                    : `${option.table_name}.${field} <= :val${num}`, {
                        [`val${num}`]: value,
                    });
                }
                if (condition === SqlWhereConditionEnum.not) {
                    if (isNil(value) || value === 'null') {
                        option.queryBuilder.andWhere(isNil(option.table_name) ? `${field} is not null`
                    : `${option.table_name}.${field} is not null`);
                    } else {
                        option.queryBuilder.andWhere(isNil(option.table_name) ? `${field} <> :val${num}` 
                        : `${option.table_name}.${field} <> :val${num}`, {
                            [`val${num}`]: value,
                        });
                    }
                }
                if (condition === SqlWhereConditionEnum.notIn) {
                    option.queryBuilder.andWhere(isNil(option.table_name) ? `${field} not in (:...val${num})` 
                    : `${option.table_name}.${field} not in (:...val${num})`, {
                        [`val${num}`]: value,
                    });
                }
            });
        });
    }
    /* if (option.query.take) {
        option.queryBuilder.take(option.query.take);
    }
    if (option.query.skip) {
        option.queryBuilder.skip(option.query.skip);
    }

    if (option.query.select) {
        if (option.table_name) { // 添加表别名
            for (let index = 0; index < option.query.select.length; index++) {
                const element = option.query.select[index];
                option.query.select[index] = `${option.table_name}.${element}`;
            }
        }
        option.queryBuilder.select(option.query.select);
    } */
}
/**
 * 处理 take、skip、select
 * @param option 
 */
export function selectAndTakeQueryBuilder(option: SqlQueryBuilderOption): void {
    if (option.query.take) {
        option.queryBuilder.take(option.query.take);
    }
    if (option.query.skip) {
        option.queryBuilder.skip(option.query.skip);
    }

    if (option.query.select) {
        if (option.table_name) { // 添加表别名
            for (let index = 0; index < option.query.select.length; index++) {
                const element = option.query.select[index];
                option.query.select[index] = `${option.table_name}.${element}`;
            }
        }
        option.queryBuilder.select(option.query.select);
    } else {
        option.queryBuilder.select(option.table_name);
    }
}

/* export function groupQueryBuilder(option: SqlQueryBuilderOption): void {
    if (option.query.group) {
        if (option.table_name) {

        }
    }
}
 */


/**
 * 处理relation
 * @param option 
 */
export function relationQueryBuilder(option: SqlQueryBuilderOption): void {
    
    if (option.query.left) {
        for (let index = 0; index < option.query.left.length; index++) {
            const element = option.query.left[index];
            
            option.queryBuilder.leftJoinAndSelect(
                // element.mapToProperty,
                element.property,
                element.alias,
                element.condition,
                element.parameters
            );    
        }
        
    }
}


export function countQueryBuilder(option: SqlQueryBuilderOption): void {
    option.queryBuilder.take(0);
    option.queryBuilder.skip(0);
    option.queryBuilder.orderBy();
    option.queryBuilder.select(`count(1)`, 'count');
}